package com.ai.smlcore.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.ai.frame.bean.OutputObject;
import com.ai.frame.logger.Logger;
import com.ai.frame.logger.LoggerFactory;

public class ExcelPOI07Util {
    private static final Logger logger = LoggerFactory.getUtilLog(ExcelPOI07Util.class);

	public static final int rowaccess = 100;// 内存中缓存记录行数

	public static void main(String[] args) {
		FileOutputStream fileOutputStream = null;
		try (SXSSFWorkbook wb = new SXSSFWorkbook();){
			fileOutputStream = new FileOutputStream("C:/ftl/export/file/tmp/cs.xlsx");

			OutputObject outObj = new OutputObject();

			String headCnName = "订单号,源订单号,订单来源渠道,订单状态,厂商状态,快递单号,手机串号,支付状态,支付方式,订单实收总金额,订购个数,商品码,商品品类,商品品牌,商品名称,商品商家,源订单创建时间,订单创建时间,订单支付时间,收货人姓名,收货人手机号码,收货人-省,收货人-市,收货人-区,收货人地址,来电手机号码,来电客户姓名,发票姓名,备注,下单工号,是否已结算";
			String dataEnName = "odrId,coopPrnrOdrNo,chnlNm,odrStsNm,odrStsNm,ehsBlId,IMEI,chgpyFlagNm,paymnTypeNm,realAmt,sbscrMcdsQty,suplerProdCode,catgUnitNm1,catgUnitNm,mcdsUnitNm,ptyNm,rsvtDate,crtTime,payTime,revrNm,mobnum,province,city,county,addr,custResvTelNum,custName,tinvcInvttNm,odrRmk,userLoginId,sttlFlag";
			String dataSize = "30,20,20,20,20,20,20,20,20,20,20,20,20,20,20,50,20,20,20,30,20,20,20,20,20,30,20,20,20,20,20";
			outObj.getBean().put("excel_head_cnname", headCnName);
			outObj.getBean().put("excel_data_enname", dataEnName);
			outObj.getBean().put("excel_data_cellSize", dataSize);

			ExcelPOI07Util.createExcel2007(true, wb, fileOutputStream, outObj);

			for (int j = 0; j < 50; j++) {
				List<Map<String, String>> list = new ArrayList<Map<String, String>>();
				for (int i = 0; i < 100; i++) {
					Map<String, String> data = new HashMap<String, String>();
					data.put("odrId", "11#" + i);
					data.put("coopPrnrOdrNo", "22#" + i);
					data.put("chnlNm", "33#" + i);
					list.add(data);
				}
				outObj.setBeans(list);
				ExcelPOI07Util.createExcel2007(false, wb, fileOutputStream, outObj);
			}

			wb.write(fileOutputStream);
		} catch (Exception e) {
			logger.error("", "", e);
		} finally {
			try {
				if (fileOutputStream != null) {
					fileOutputStream.close();
				}
			} catch (IOException e) {
				logger.error("", "", e);
			}
		}

	}

	public static void createExcel2007(Boolean isNewSheet, SXSSFWorkbook wb, OutputStream out, OutputObject outObj) {
		try {
			String headStr = outObj.getBean().get("excel_head_cnname");// 标题
			SXSSFSheet sheet = null;
			if (isNewSheet) {
				sheet = (SXSSFSheet) wb.createSheet();
			} else {
				sheet = (SXSSFSheet) wb.getSheetAt(0);
			}
			int startR = 0;
			Map<String, CellStyle> styleMap = createStyles(wb);
			if (!StringUtil.isEmpty(headStr) && isNewSheet) {

				CellStyle headStyle = styleMap.get("sheet_title_style");

				String[] headArr = headStr.split(",");
				String cellSizeStr = outObj.getBean().get("excel_data_cellSize");// 宽度
				if (!StringUtil.isEmpty(cellSizeStr)) {
					String[] cellSizeArr = cellSizeStr.split(",");
					createExcelHeadNormal(sheet, startR, headArr, cellSizeArr, headStyle);
				} else {
					createExcelHeadNormal(sheet, startR, headArr, headStyle);
				}
				startR++;
			}
			String dataNameStr = outObj.getBean().get("excel_data_enname");// 内容
			if (!StringUtil.isEmpty(dataNameStr) && !isNewSheet) {
				int physicalRowNum = sheet.getPhysicalNumberOfRows();
				System.out.println("physicalRowNum:" + physicalRowNum);
				startR = physicalRowNum;
				CellStyle cellStyle = styleMap.get("cell_normal_style");
				String[] dataEnNameArr = dataNameStr.split(",");
				List<Map<String, String>> dataList = outObj.getBeans();
				createExcelBody(sheet, startR, dataEnNameArr, dataList, cellStyle);
			}
			// wb.write(out);
		} catch (Exception e) {
			logger.error("", "", e);
		}
	}

	public static void createExcel2007(OutputStream out, OutputObject outObj) {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			String headStr = outObj.getBean().get("excel_head_cnname");// 标题
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet();
			int startR = 0;
			Map<String, CellStyle> styleMap = createStyles(wb);
			if (!StringUtil.isEmpty(headStr)) {

				CellStyle headStyle = styleMap.get("sheet_title_style");

				String[] headArr = headStr.split(",");
				String cellSizeStr = outObj.getBean().get("excel_data_cellSize");// 宽度
				if (!StringUtil.isEmpty(cellSizeStr)) {
					String[] cellSizeArr = cellSizeStr.split(",");
					createExcelHeadNormal(sheet, startR, headArr, cellSizeArr, headStyle);
				} else {
					createExcelHeadNormal(sheet, startR, headArr, headStyle);
				}
				startR++;
			}
			String dataNameStr = outObj.getBean().get("excel_data_enname");// 内容
			if (!StringUtil.isEmpty(dataNameStr)) {
				CellStyle cellStyle = styleMap.get("cell_normal_style");
				String[] dataEnNameArr = dataNameStr.split(",");
				List<Map<String, String>> dataList = outObj.getBeans();
				createExcelBody(sheet, startR, dataEnNameArr, dataList, cellStyle);
			}
			wb.write(out);
		} catch (Exception e) {
			logger.error("", "", e);
		} finally {
			try {
				wb.close();
			} catch (IOException e) {
				logger.error("", "", e);
			}
		}
	}

	public static void createExcel2007MergeCells(OutputStream out, OutputObject outObj) {
		Workbook wb = new SXSSFWorkbook();
		try {
			String headStr = outObj.getBean().get("excel_head_cnname");// 标题
			Sheet sheet = wb.createSheet();
			int startR = 0;
			Map<String, CellStyle> styleMap = createStyles(wb);
			if (!StringUtil.isEmpty(headStr)) {
				CellStyle headStyle = styleMap.get("newsheet_title_style");
				String[] headArr = headStr.split(",");
				String cellSizeStr = outObj.getBean().get("excel_data_cellSize");// 宽度

				if (!StringUtil.isEmpty(cellSizeStr)) {
					startR = createExcelHeadMergeCells(sheet, startR, headArr, cellSizeStr.split(","), headStyle);
				} else {
					startR = createExcelHeadMergeCells(sheet, startR, headArr, null, headStyle);
				}
			}

			String dataNameStr = outObj.getBean().get("excel_data_enname");// 内容
			if (!StringUtil.isEmpty(dataNameStr)) {
				CellStyle cellStyle = styleMap.get("cell_normal_style");
				String[] dataEnNameArr = dataNameStr.split(",");
				List<Map<String, String>> dataList = outObj.getBeans();
				createExcelBody(sheet, startR, dataEnNameArr, dataList, cellStyle);
			}
			wb.write(out);
		} catch (Exception e) {
			logger.error("", "", e);
		} finally {
			try {
				wb.close();
			} catch (IOException e) {
				logger.error("", "", e);
			}
		}
	}

	public static void createExcelBody(Sheet sheet, int startR, String[] dataEnNameArr,
			List<Map<String, String>> dataList, CellStyle normalCellStyle) throws Exception {
		for (int i = 0, len = dataList.size(); i < len; i++) {
			Row row = sheet.createRow(startR + i);
			for (int j = 0, jlen = dataEnNameArr.length; j < jlen; j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(StringUtil.trim2Empty(dataList.get(i).get(dataEnNameArr[j])));
				cell.setCellStyle(normalCellStyle);
			}

			// 每当行数达到设置的值就刷新数据到硬盘,以清理内存
			if (i % rowaccess == 0) {
				((SXSSFSheet) sheet).flushRows();
			}

		}

	}

	public static void createExcelHeadNormal(Sheet sheet, int startR, String[] headArr, CellStyle headStyle)
			throws Exception {
		Row row = sheet.createRow(startR);
		row.setHeight((short) 500);
		for (int i = 0, len = headArr.length; i < len; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(headArr[i]);
			cell.setCellStyle(headStyle);
		}
		for (int i = 0, len = headArr.length; i < len; i++) {
			sheet.setColumnWidth(i, 20 * 256);
		}
	}

	/***
	 * 根据格式 合并 头部 单元格 支持多行、多列 复杂的合并 如果有格式问题，联系 殊風
	 * 
	 * @param sheet
	 * @param startR
	 * @param headArr
	 * @param headStyle
	 * @throws Exception
	 */
	public static int createExcelHeadMergeCells(Sheet sheet, int startR, String[] headArr, String[] cellSizeArr,
			CellStyle headStyle) throws Exception {
		int HeadRowCnt = 0, tmpval;
		for (int i = 0; i < headArr.length - 1; i++) {
			tmpval = headArr[i].split("\\|").length;

			if (tmpval > HeadRowCnt) {
				HeadRowCnt = tmpval;
			}
		}

		List<String[]> headlist = new ArrayList<>();
		for (int i = 0; i < headArr.length; i++) {
			String[] headArray = new String[HeadRowCnt];
			String[] tmpheadArray = headArr[i].split("\\|");
			int deci = headArray.length - 1;
			for (int j = tmpheadArray.length - 1; j >= 0; j--) {
				headArray[deci--] = tmpheadArray[j];
			}
			for (int j = 0; j < headArray.length; j++) {
				if (headArray[j] == null || headArray[j].equals("")) {
					headArray[j] = tmpheadArray[0];
				} else {
					break;
				}
			}
			headlist.add(headArray);
		}
		Row[] rows = new Row[HeadRowCnt];
		for (int i = 0; i < HeadRowCnt; i++) {
			rows[i] = sheet.createRow(startR + i);
			rows[i].setHeight((short) 300);
		}
		for (int col = 0; col < headArr.length; col++) {
			String[] Cols = headlist.get(col);
			for (int row = 0; row < Cols.length; row++) {
				Cell cell = rows[row].createCell(col);
				char c = Cols[row].charAt(Cols[row].length() - 1);
				if ('*' == c) {
					cell.setCellValue(Cols[row].substring(0, Cols[row].length() - 1));
					Cols[row] = "";
				} else {
					cell.setCellValue(Cols[row]);
				}
				cell.setCellStyle(headStyle);
			}
		}
		// 循环开始合并 Col 与 Row 都是从0开始
		int endrow = 0, endcol = 0, tmpcol = 0;
		for (int row = 0; row < HeadRowCnt; row++) {
			for (int col = 0; col < headArr.length; col++) {

				if (!headlist.get(col)[row].equals("")) {
					tmpcol = col;
					for (int i = row; i < HeadRowCnt; i++) // 行
					{
						for (int j = col; j < headArr.length; j++) // 列
						{
							if (!headlist.get(col)[row].equals(headlist.get(j)[i])) {
								tmpcol = j - 1;
								break;
							}
						}
						if (i == row) {
							endcol = tmpcol;
						} else if (endcol != tmpcol) {
							break;
						}
						endrow = i;
					}
					// 是否符合合并条件
					if ((endcol > col) || (endrow > row)) {
						// 合并 并清空 区域内值
						sheet.addMergedRegion(new CellRangeAddress(row, endrow, col, endcol));
						for (int i = row; i <= endrow; i++) {
							for (int j = col; j <= endcol; j++) {
								headlist.get(j)[i] = "";
							}
						}
					}
				}
			}
		}

		/*
		 * if (cellSizeArr != null){ for (int i = 0, len = headArr.length; i <
		 * len; i++) { sheet.setColumnWidth(i,str2Int(cellSizeArr[i], 20) *
		 * 256); } }
		 */

		for (int i = 0; i < headArr.length; i++) {
			sheet.setColumnWidth(i, 20 * 256);
			sheet.autoSizeColumn((short) i);
		}
		return HeadRowCnt;
	}

	public static void createExcelHeadNormal(Sheet sheet, int startR, String[] headArr, String[] cellSizeArr,
			CellStyle headStyle) throws Exception {
		Row row = sheet.createRow(startR);
		row.setHeight((short) 500);
		for (int i = 0, len = headArr.length; i < len; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(headArr[i]);
			cell.setCellStyle(headStyle);
		}
		for (int i = 0, len = headArr.length; i < len; i++) {
			sheet.setColumnWidth(i, str2Int(cellSizeArr[i], 20) * 256);
		}
	}

	public static int str2Int(String s, int defaultVal) {
		try {
			return Integer.parseInt(s);
		} catch (Exception e) {
			return defaultVal;
		}

	}

	// 创建Excel样式
	private static Map<String, CellStyle> createStyles(Workbook wb) {
		Map<String, CellStyle> stylesMap = new HashMap<String, CellStyle>();

		CellStyle style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setWrapText(true);
		stylesMap.put("cell_normal_style", style);

		Font sheetFont = wb.createFont();
		sheetFont.setFontName("宋体");
		sheetFont.setFontHeightInPoints((short) 12);
		sheetFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

		style = wb.createCellStyle();
		style.setFont(sheetFont);
		style.setWrapText(true);
		style.setFillForegroundColor(IndexedColors.LIME.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		stylesMap.put("sheet_title_style", style);

		style = wb.createCellStyle();
		style.setFont(sheetFont);
		style.setWrapText(true);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		stylesMap.put("newsheet_title_style", style);

		return stylesMap;
	}

}
